# coding: utf-8
#数据库连接对象
import MySQLdb
import MySQLdb.cursors
import re

class DB:
    def __init__(self, host, username, pwd, db, port = 3306) :
        self.myHost = host
        self.myUsername = username
        self.myPassword = pwd
        self.myDatabase = db
        if host != "mysql2" : self.myPort = int(port)
        else : self.myPort = 3307
        self.connect()
        self.cursor = None

    def connect(self):
        self.conn = MySQLdb.connect(host=self.myHost, user=self.myUsername, passwd=self.myPassword, db=self.myDatabase, port = self.myPort, charset='utf8')

    #处理分表，已被sql_util中horizonal_sql代替
    def preprocess_sql(self, sql, data = None) :
        tablename_match = re.search("from[ ]+(\S+)", sql)
        if not tablename_match : return [(sql, data)]
        table_name = tablename_match.group(1)
        if table_name in ["plan_oneday_h", "plan_onedayevent_h"] :
            if data : query_sql = sql % tuple(map(str, data))
            else : query_sql = sql
            plan_id_match = re.search("plan_id[ ]+=[ ]+(\d+)", query_sql)
            if plan_id_match :
                plan_id = plan_id_match.group(1)
                if table_name == "plan_oneday_h" : table_index = int(plan_id) % 20
                else : table_index = int(plan_id) % 50
                sql = sql.replace(table_name, "%s_%d" % (table_name, table_index))
                return [(sql, data)]
                #res_sql_datas.append((sql, data))
            else :
                plan_ids_match = re.search(r"plan_id[ ]+in[ ]+\(([\d ,]*)\)", query_sql)
                if plan_ids_match :
                    res_sql_datas = []
                    tableindex_planids_dict = {}
                    plan_ids = plan_ids_match.group(1).split(",")
                    for plan_id in plan_ids :
                        if table_name == "plan_oneday_h" : table_index = int(plan_id) % 20
                        else : table_index = int(plan_id) % 50
                        if table_index not in tableindex_planids_dict : tableindex_planids_dict[table_index] = [plan_id]
                        else : tableindex_planids_dict[table_index].append(plan_id)
                    for tableindex, planids in tableindex_planids_dict.iteritems() :
                        new_sql = sql.replace(table_name, "%s_%d" % (table_name, tableindex)).replace(plan_ids_match.group(0), "plan_id in (%s)" % ",".join(planids))
                        res_sql_datas.append((new_sql, None))
                    if len(res_sql_datas) : return res_sql_datas
                    else : return [(sql, data)]
                else : return [(sql, data)]
        else : return [(sql, data)]

    #执行sql, 查询参数为data
    def execute(self, sql, data = None) :
        try :
            self.cursor = self.conn.cursor()
            if data :
                self.cursor.execute(sql, data)
                self.conn.commit()
            else :
                self.cursor.execute(sql)
                self.conn.commit()
        except (AttributeError, MySQLdb.OperationalError) :
            self.connect()
            self.cursor = self.conn.cursor()
            if data :
                self.cursor.execute(sql, data)
                self.conn.commit()
            else :
                self.cursor.execute(sql)
                self.conn.commit()
        return

    #执行多条语句, datalist
    def executemany(self, sql, datalist) :
        try :
            self.cursor = self.conn.cursor()
            self.cursor.executemany(sql, datalist)
            self.conn.commit()
        except (AttributeError, MySQLdb.OperationalError) :
            self.connect()
            self.cursor = self.conn.cursor()
            self.cursor.executemany(sql, datalist)
            self.conn.commit()

    #获取一条结果
    def fetchone(self) :
        if self.cursor :
            result = self.cursor.fetchone()
            self.cursor.close()
            self.cursor = None
            return result
        else : return None

    #获取所有结果
    def fetchall(self) :
        if self.cursor :
            res_set = self.cursor.fetchall()
            self.cursor.close()
            self.cursor = None
            return res_set
        else : return None

    #获取多条结果
    def fetchmany(self, rownum) :
        if self.cursor :
            res_set = self.cursor.fetchmany(rownum)
            if len(res_set) < rownum :
                self.cursor.close()
                self.cursor = None
            return res_set
        else : return None

    #关闭数据库连接
    def close(self):
        if self.cursor : self.cursor.close()
        if self.conn : self.conn.close()
